{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# encoding: utf-8\n",
    "\n",
    "import pandas as pd\n",
    "from pandas import *\n",
    "import datetime\n",
    "import json\n",
    "from pymongo import MongoClient\n",
    "from collections import defaultdict\n",
    "\n",
    "pd.set_option('display.width', None)  # 设置字符显示宽度\n",
    "pd.set_option('display.max_rows', None)  # 设置显示最大行\n",
    "pd.set_option('display.max_columns', None)  # 设置显示最大行\n",
    "\n",
    "client = MongoClient('localhost', 27017)\n",
    "db = client.futures\n",
    "indexMarket = db.indexMarket\n",
    "peak = db.peak\n",
    "unit=db.unit\n",
    "\n",
    "start='20190601'\n",
    "# var='JD'\n",
    "\n",
    "indexMarket = DataFrame(list(indexMarket.find({'date': {'$gte': start}})))\n",
    "\n",
    "unit = DataFrame(list(unit.find()))\n",
    "dd=unit['variety']\n",
    "# dd\n",
    "\n",
    "# for i in dd:\n",
    "#     df=indexMarket[indexMarket['variety']==i]\n",
    "# df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "IF\n",
      "3989.0 3571.0\n",
      "IH\n",
      "3043.0 2718.0\n",
      "IC\n",
      "5256.0 4514.0\n",
      "TF\n",
      "100.0 99.0\n",
      "T\n",
      "100.0 98.0\n",
      "TS\n",
      "100.0 100.0\n",
      "CU\n",
      "48367.0 45743.0\n",
      "AU\n",
      "364.0 315.0\n",
      "AG\n",
      "4845.0 3691.0\n",
      "ZN\n",
      "19769.0 18265.0\n",
      "AL\n",
      "14657.0 13708.0\n",
      "RU\n",
      "12271.0 10556.0\n",
      "RB\n",
      "4027.0 3245.0\n",
      "FU\n",
      "3000.0 2075.0\n",
      "HC\n",
      "3912.0 3239.0\n",
      "BU\n",
      "3429.0 2884.0\n",
      "PB\n",
      "17526.0 16047.0\n",
      "NI\n",
      "149080.0 105445.0\n",
      "SN\n",
      "145088.0 127830.0\n",
      "WR\n",
      "4574.0 3598.0\n",
      "SC\n",
      "496.0 402.0\n",
      "A\n",
      "3642.0 3314.0\n",
      "B\n",
      "3475.0 2999.0\n",
      "BB\n",
      "185.0 146.0\n",
      "C\n",
      "1964.0 1823.0\n",
      "CS\n",
      "2380.0 2178.0\n",
      "FB\n",
      "73.0 56.0\n",
      "I\n",
      "878.0 570.0\n",
      "J\n",
      "2221.0 1731.0\n",
      "JD\n",
      "4804.0 4105.0\n",
      "JM\n",
      "1423.0 1203.0\n",
      "L\n",
      "7876.0 7033.0\n",
      "M\n",
      "3057.0 2748.0\n",
      "P\n",
      "5173.0 4221.0\n",
      "PP\n",
      "8801.0 7762.0\n",
      "V\n",
      "6892.0 6276.0\n",
      "Y\n",
      "6322.0 5428.0\n",
      "WH\n",
      "2479.0 2119.0\n",
      "PM\n",
      "2398.0 2055.0\n",
      "CF\n",
      "13610.0 12010.0\n",
      "CY\n",
      "21941.0 19805.0\n",
      "SR\n",
      "5634.0 5104.0\n",
      "TA\n",
      "5978.0 4821.0\n",
      "OI\n",
      "7583.0 6899.0\n",
      "RI\n",
      "2980.0 2326.0\n",
      "MA\n",
      "2431.0 2062.0\n",
      "FG\n",
      "1518.0 1373.0\n",
      "RS\n",
      "4131.0 3457.0\n",
      "RM\n",
      "2485.0 2234.0\n",
      "ZC\n",
      "595.0 552.0\n",
      "JR\n",
      "3441.0 2802.0\n",
      "LR\n",
      "2894.0 2459.0\n",
      "SF\n",
      "6449.0 5711.0\n",
      "SM\n",
      "7604.0 6278.0\n",
      "AP\n",
      "9769.0 7355.0\n",
      "EG\n",
      "5336.0 4254.0\n",
      "CJ\n",
      "11546.0 9780.0\n",
      "SP\n",
      "4867.0 4433.0\n"
     ]
    }
   ],
   "source": [
    "for i in dd:\n",
    "    df=indexMarket[indexMarket['variety']==i]\n",
    "# df=pd.DataFrame(df)\n",
    "# print(df.tail(5))\n",
    "    df= df[['date', 'variety','set_open','set_close', 'set_high', 'set_low']]\n",
    "\n",
    "# df['date'] = pd.to_datetime(df['date'])\n",
    "    df.set_index('date',inplace=True)\n",
    "#     df.tail(2)\n",
    "    maxs=df[['set_high','set_low']].stack().max()\n",
    "    mins=df[['set_high','set_low']].stack().min()\n",
    "    print(i)\n",
    "    print(maxs,mins)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4867.0 4433.0\n"
     ]
    }
   ],
   "source": [
    "maxs=df[['set_high','set_low']].stack().max()\n",
    "mins=df[['set_high','set_low']].stack().min()\n",
    "print(maxs,mins)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9.79"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gains=round(((maxs/mins-1)*100),2)\n",
    "gains"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8.92"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lesses=round(((1-mins/maxs)*100),2)\n",
    "lesses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# df['one'] = df[\"one\"].apply(lambda x: x*10 if x<2 else (x**2 if x<4 else x+10))\n",
    "\n",
    "gains=round(((maxs/mins-1)*100),2)\n",
    "lesses=round(((1-mins/maxs)*100),2)\n",
    "\n",
    "df['peak']=(lambda x:gains if mins < df['set_close'][-1] else lesses)(1)\n",
    "# print(peak(1))\n",
    "# df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "# if mins>df['set_close'][-1]:\n",
    "#     print(gains)\n",
    "\n",
    "# else:\n",
    "#     print(lesses)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# date=df.append([maxs],[peak(1)])#.fillna(method='ffill')#,columns=['peak']\n",
    "# print(date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>variety</th>\n",
       "      <th>new_close</th>\n",
       "      <th>maxs</th>\n",
       "      <th>mins</th>\n",
       "      <th>gains</th>\n",
       "      <th>lesses</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20191025</th>\n",
       "      <td>SP</td>\n",
       "      <td>4632.0</td>\n",
       "      <td>4867.0</td>\n",
       "      <td>4433.0</td>\n",
       "      <td>9.79</td>\n",
       "      <td>8.92</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         variety  new_close    maxs    mins  gains  lesses\n",
       "date                                                      \n",
       "20191025      SP     4632.0  4867.0  4433.0   9.79    8.92"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = DataFrame()\n",
    "for i in dd:\n",
    "    df=indexMarket[indexMarket['variety']==i]\n",
    "# df=pd.DataFrame(df)\n",
    "# print(df.tail(5))\n",
    "    date=df[['date'][-1]]\n",
    "\n",
    "    data= df[['date', 'variety','set_open','set_close', 'set_high', 'set_low']]\n",
    "# df\n",
    "# df['date'] = pd.to_datetime(df['date'])\n",
    "    data.set_index('date',inplace=True)\n",
    "#     ss=data['set_close']\n",
    "# ss\n",
    "#     df.tail(2)\n",
    "    maxs=data[['set_high','set_low']].stack().max()\n",
    "# maxs\n",
    "    mins=data[['set_high','set_low']].stack().min()\n",
    "    gains=round(((maxs/mins-1)*100),2)\n",
    "    lesses=round(((1-mins/maxs)*100),2)\n",
    "\n",
    "    peak=(lambda x:gains if mins < data['set_close'][-1] else lesses)(1)\n",
    "# peak\n",
    "#     df['peak']=(lambda x:gains if mins < df['set_close'][-1] else lesses)(1)\n",
    "#     df=df[-1:]\n",
    "# df\n",
    "#     peak=peak(1)\n",
    "#     df2=df2.append(pd.DataFrame([maxs],[date]))\n",
    "#     df2=df2.append({'maxs':maxs,'mins':mins,'gains':gains,'lesses':lesses},date)\n",
    "    columns=list(['variety','new_close','maxs','mins','gains','lesses'])\n",
    "    data2 = pd.DataFrame([[i,data['set_close'][-1],maxs,mins,gains,lesses]],date, columns)[-1:]\n",
    "\n",
    "#     data2=pd.DataFrame({'maxs':maxs,'mins':mins,'gains':gains,'lesses':lesses,'peak':peak},date)[-1:]\n",
    "#     data2=df2.append(data2)\n",
    "data2\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# df2=pd.DataFrame()\n",
    "# df3={}\n",
    "for i in set(dd):\n",
    "    try:\n",
    "        df=indexMarket[indexMarket['variety']==i]\n",
    "#         print(df)\n",
    "        date=df[['date'][-1]]\n",
    "        data= df[['date', 'variety','set_open','set_close', 'set_high', 'set_low']]\n",
    "        data.set_index('date',inplace=True)\n",
    "        maxs=data[['set_high','set_low']].stack().max()\n",
    "        mins=data[['set_high','set_low']].stack().min()\n",
    "        \n",
    "        gains=round(((maxs/mins-1)*100),2)\n",
    "        lesses=round(((1-mins/maxs)*100),2)\n",
    "        peak=(lambda x:gains if mins < data['set_close'][-1] else lesses)(1)\n",
    "#         print(peak)\n",
    "        columns=list(['variety','new_close','maxs','mins'])\n",
    "        data2 = pd.DataFrame([[i,data['set_close'][-1],maxs,mins],date,columns])[-1:]\n",
    "        print(data2)\n",
    "#         data2=df2.append(data2)\n",
    "#         \n",
    "#         data2 = data2.reset_index()\n",
    "        \n",
    "        \n",
    "#         data=data[['variety','maxs','mins','set_close','peak']]\n",
    "\n",
    "#         data=data[-1:]\n",
    "# #         data=pd.concat(data)\n",
    "# #         data.append(data)\n",
    "#         print(data)\n",
    "        \n",
    "        \n",
    "    except:\n",
    "        pass\n",
    "        continue\n",
    "# for key, value in data2.items():\n",
    "#             print(key)\n",
    "#         data2[key].append[]???\n",
    "        \n",
    "                              \n",
    "    \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2=pd.DataFrame()\n",
    "for i in set(dd):\n",
    "    try:\n",
    "        df=indexMarket[indexMarket['variety']==i]\n",
    "        date=df[['date'][-1]]\n",
    "        data= df[['date', 'variety','set_open','set_close', 'set_high', 'set_low']]\n",
    "        data.set_index('date',inplace=True)\n",
    "        maxs=data[['set_high','set_low']].stack().max()\n",
    "        mins=data[['set_high','set_low']].stack().min()\n",
    "        gains=round(((maxs/mins-1)*100),2)\n",
    "        lesses=round(((1-mins/maxs)*100),2)\n",
    "        peak=(lambda x:gains if mins < data['set_close'][-1] else lesses)(1)\n",
    "        columns=list(['variety','new_close','maxs','mins'])\n",
    "#         data2 = pd.DataFrame([[i,data['set_close'][-1],maxs,mins],date,columns])[-1:]\n",
    "\n",
    "        data=data.copy()\n",
    "        \n",
    "        data['maxs']=maxs\n",
    "        data['mins']=mins\n",
    "        data['peak']=peak\n",
    "        data=data[-1]\n",
    "        print(data)\n",
    "        \n",
    "        \n",
    "    except:\n",
    "        pass\n",
    "#         continue\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n",
      "error\n"
     ]
    }
   ],
   "source": [
    "for i in set(dd):\n",
    "    try:\n",
    "        df=indexMarket[indexMarket['variety']==i]\n",
    "#         date=df[['date'][-1]]\n",
    "        df= df[['date', 'variety','set_open','set_close', 'set_high', 'set_low']]\n",
    "        df.set_index('date',inplace=True)\n",
    "        maxs=df[['set_high','set_low']].stack().max()\n",
    "        mins=df[['set_high','set_low']].stack().min()\n",
    "        gains=round(((maxs/mins-1)*100),2)\n",
    "        lesses=round(((1-mins/maxs)*100),2)\n",
    "        peak=(lambda x:gains if mins < data['set_close'][-1] else lesses)(1)\n",
    "        df2=df.copy()\n",
    "        df2['maxs']=maxs\n",
    "        df2['mins']=mins\n",
    "        df2['peak']=peak\n",
    "#         df2 = df2.reset_index()\n",
    "        df2=df2[['variety','maxs','mins','set_close','peak']][-1:]\n",
    "#         df2['date']=df\n",
    "#         \n",
    "#         df2=pd.DataFrame(df2)\n",
    "    \n",
    "#         df2=peak.insert(df2)\n",
    "#         print(df2)\n",
    "        peak.insert(json.loads(df2.T.to_json()).values())\n",
    "        print(json.loads(df2.T.to_json()).values())\n",
    "        print(1)\n",
    "    except:\n",
    "        print('error')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
